package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Score;
import util.ConnectionUtil;

public class ScoreDao {

	public static void add(int stu_id, int course_id, int score) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "insert into score (stu_id, course_id, score) VALUES (?, ?, ?)";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, stu_id);
			ps.setInt(2, course_id);
			ps.setInt(3, score);
			int result = ps.executeUpdate();
			if(result > 0) {
				System.out.println("选课成功");				
			}else {
				System.out.println("选课失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public static ArrayList<Score> getScoreList(int studentid) {
		ArrayList<Score> scList = new ArrayList<>();
		
		Connection connection = ConnectionUtil.getConnection();
		String sql = "select student.stu_name, course.coures_name, score.score from score, student, course where score.stu_id = ? and score.stu_id = student.stu_id and score.course_id = course.course_id";
		try {
			PreparedStatement psta = connection.prepareStatement(sql);
			psta.setInt(1, studentid);
			ResultSet rs = psta.executeQuery();
			
			while(rs.next()) {
				String stuName = rs.getString(1);
				String courseName = rs.getString(2);
				int scoreNum = rs.getInt(3);
				
				Score score = new Score(stuName, courseName, scoreNum);
				scList.add(score);
			}
			
			psta.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return scList;
	}

}
